# -*- coding: utf-8 -*-
"""
Created on Wed Jun 28 19:06:19 2017

@author: ahchpr

filename: static_sprot.py
"""

import mysql.connector
#import numpy as np
import pandas as pd 
#import xlwt
#import xlrd
import os
import datetime
from sqlalchemy import create_engine

config = {'user':'root', 
          'password':'Hz2mEJxG', 
          'host':'192.168.188.107', 
          'port':3306,  
          'database':'sport'}                                                                  
                                                                     
def compare_time(file_name):
    """考虑把同一个目录里的所有log文件都当做要处理的文件
    """
    today = datetime.date.today()
#    today = today - datetime.timedelta(days=1)
    file_path = os.path.dirname(file_name)
    name = file_name[len(file_path)+1:]
    file_date_list = name.split(".")
    if file_date_list[1] == 'xls':
      file_date = datetime.datetime.strptime(file_date_list[0], "%Y_%m_%d").date()
      if file_date <= today:
        return True
      else:
        return False


def get_xls(rootdir):
  xls_file_list = []
  for p, d, f in os.walk(rootdir):
    for i in f:
      file_name = os.path.join(p, i)
      if compare_time(file_name):
        xls_file_list.append(file_name) 
  return (xls_file_list)

    
def read_xls(xls_file):
  df = pd.read_excel(xls_file)
  return (df)
  
def connect_mysql(config):
  conn = mysql.connector.connect(**config)
  cur = conn.cursor()
  return (conn, cur)
  cur.execute("select version()")
  result = cur.fetchall()
  print (result)
  

def insert_mysql(conn, cur, df):
  pass


if __name__ == "__main__":
  rootdir = os.path.abspath(os.curdir) + "\\"
  xls_file_list = get_xls(rootdir)
  DB_CONNECT_STRING = r'mysql+mysqlconnector://root:Hz2mEJxG@192.168.188.107:3306/sport?charset=utf8'
  yconnect = create_engine(DB_CONNECT_STRING)
#  df_all=pd.DataFrame(columns=['dtEventTime', 'stuffName', 'walkNum', 'status'])
#  print (df_all)
  for one_xls in xls_file_list:
    print (one_xls)
    df = read_xls(one_xls) 
#    print (type(yconnect))
    pd.io.sql.to_sql(df, 'punch', yconnect, schema='sport', if_exists='append', index=False)
    